package server;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Random;

import entities.User;

import ocsf.server.ConnectionToClient;
/**
 * Query controllers for the Library Manager
 * @author Benny
 *
 */
public class ManagerQueryHandle 
{
	public mysqlConnection sqlCon;
	public User user;
	/**
	 * Constructor of the object
	 * @param c established connection to the SQL DataBase
	 */
	public ManagerQueryHandle(mysqlConnection c)
	{
		this.sqlCon=c;
	}
	/**
	 * Handling message from client and sending an answer
	 * @param msg received message from a specific client, through the running server
	 * @param client a specific client connected to the running server
	 * @throws SQLException
	 * @throws IOException
	 */
	public void handleMessage (Object msg, ConnectionToClient client) throws SQLException, IOException 
	{
		ArrayList<Object> messages=(ArrayList<Object>) msg;
		ArrayList<Object> res=new ArrayList<>();;
		String command = (String) messages.get(1);
		res.add(messages.get(0).toString()); // add role
		res.add(messages.get(1).toString()); // add type
		/**
		 * Query controller for updating a specific user's status to graduated
		 */
		if(command.equals("GraduateUser"))
		{
			Statement stmt = null;
			try {
				stmt = sqlCon.conn.createStatement();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			Statement stmtUpdt = sqlCon.conn.createStatement();
			int userID=(int)messages.get(2);
			ResultSet rs= stmt.executeQuery("SELECT * FROM borrows WHERE borrowStatus='active' AND userID='"+userID+"';");
			if(rs.next())
			{
				int result = stmtUpdt.executeUpdate("UPDATE users SET cardStatus='frozen' WHERE cardStatus='ok' AND userID='"+userID+"';");
				res.add("Fail");
				
			}
			else
			{
				int result = stmtUpdt.executeUpdate("UPDATE users SET cardStatus='graduate' WHERE cardStatus='ok' AND userID='"+userID+"';");
				if(result>0)
					res.add("OK");
				else
					res.add("Fail");
			}
			client.sendToClient(res);
		}
		/**
		 * Query controller for getting the frozen user by its userID and change his status to ok 
		 */
		if(command.equals("UnfreezeUser"))
		{
			Statement stmt = null;
			try {
				stmt = sqlCon.conn.createStatement();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			int userID=(int)messages.get(2);
			ResultSet rs = stmt.executeQuery("SELECT * FROM borrows, errors WHERE borrows.userID='"+userID+"' AND borrows.borrowID=errors.borrowErrorID AND errors.errorStatus='active'");
			if(rs.next())
			{
				res.add("Fail");
			}
			else
			{
				Statement stmtUpdt = sqlCon.conn.createStatement();
				int result = stmtUpdt.executeUpdate("UPDATE users SET cardStatus='ok' WHERE userID='"+userID+"';");
				if(result>0)
					res.add("OK");
				else
					res.add("Fail");
			}
			rs.close();
			client.sendToClient(res);
		}
		/**
		 * Query controller for getting all the users who's status is frozen
		 */
		if(command.equals("ViewFrozenUsers"))
		{
			Statement stmt = null;
			try {
				stmt = sqlCon.conn.createStatement();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			ResultSet rs = stmt.executeQuery("SELECT userID,cardStatus FROM users WHERE cardStatus='frozen';");
			if(rs.next())
			{
				res.add("OK");
				rs.previous();
				while(rs.next())
				{
						String[] frozenUser = new String[2];
						String userID = Integer.toString(rs.getInt("userID"));
						frozenUser[0]=userID;
						frozenUser[1]=rs.getString("cardStatus");
						res.add(frozenUser);
				}
			}
			else
				res.add("Fail");
			rs.close();
			client.sendToClient(res);
		}
		/**
		 * Query controller for getting all the users who are late in return date and creating an error for them
		 */
		if(command.equals("ShowUserToFreeze"))
		{
			Statement stmt = null;
			try {
				stmt = sqlCon.conn.createStatement();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			Calendar cal = Calendar.getInstance();
			DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			String today=sdf.format(cal.getTime());
			System.out.println(today);
			ResultSet rs1 = null;
			ResultSet rs = stmt.executeQuery("SELECT * FROM borrows, users WHERE borrows.borrowStatus='active' AND borrows.userID=users.userID AND users.cardStatus='ok';");
			if(rs.next())
			{
				rs.previous();
				while(rs.next())
				{
					String returnDate=rs.getString("returnDate");
					int borrowID=rs.getInt("borrowID");
					Date d1 = null;
					Date d2 = null;
					try {
						d1 = (Date)sdf.parse(returnDate);
						d2 = (Date)sdf.parse(today);
					} catch (ParseException e) {
						e.printStackTrace();
					}

					if(d1.getTime()<d2.getTime())
					{

						Statement stmtUpdt = sqlCon.conn.createStatement();
						System.out.println(borrowID);
						Statement st = null;
						st = sqlCon.conn.createStatement();
						rs1=st.executeQuery("SELECT borrowErrorID FROM errors WHERE borrowErrorID='"+borrowID+ "'AND errorStatus='active';");
						int flag=0;
						int result;
						if(rs1.next())
						{
							int borrowErrorID;
							rs1.previous();
							while(rs1.next())
							{
								borrowErrorID=rs1.getInt("borrowErrorID");
								if(borrowErrorID==borrowID)
								{
									flag=1;
									break;
								}
							}
							if(flag==0)
							{
								result = stmtUpdt.executeUpdate("INSERT INTO errors (errorStatus, errorDate, borrowErrorID) VALUES('active','"+ today+ "','"+ borrowID +"');");
							}
						}
						else
							result = stmtUpdt.executeUpdate("INSERT INTO errors (errorStatus, errorDate, borrowErrorID) VALUES('active','"+ today+ "','"+ borrowID +"');");
					}
				}
			}
			else
				res.add("Fail");
			Statement st2;
			st2=sqlCon.conn.createStatement();
			ResultSet rs2 = st2.executeQuery("SELECT DISTINCT borrows.borrowID, borrows.copyID, borrows.userID, borrows.returnDate FROM errors, borrows WHERE errors.errorStatus='active' AND errors.borrowErrorID=borrows.borrowID;");
			
			if(rs2.next())
			{
				res.add("OK");
				rs2.previous();
				while(rs2.next())
				{
					String[] userToFreeze = new String[4];
					String userID = Integer.toString(rs2.getInt("borrows.userID"));
					String copyID = Integer.toString(rs2.getInt("borrows.copyID"));
					userToFreeze[0]=userID;
					userToFreeze[1]=copyID;
					userToFreeze[2]=rs2.getString("borrows.returnDate");
					userToFreeze[3]=today;
					res.add(userToFreeze);
					System.out.println("add:");
					System.out.println(copyID);
				}
				
			}
			rs2.close();
			rs1.close();
			rs.close();
			client.sendToClient(res);
		}
		/**
		 * Query controller for changing the users who are late in returns status to frozen
		 */
		if(command.equals("FreezeAll"))
		{
			Statement stmt = null;
			try {
				stmt = sqlCon.conn.createStatement();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			Calendar cal = Calendar.getInstance();
			DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			String today=sdf.format(cal.getTime());
			ResultSet rs = stmt.executeQuery("SELECT userID,copyID,returnDate FROM borrows WHERE borrowStatus='active';");
			if(rs.next())
			{
				res.add("OK");
				rs.previous();
				while(rs.next())
				{
					String returnDate=rs.getString("returnDate");
					Date d1 = null;
					Date d2 = null;
					try {
						d1 = (Date)sdf.parse(returnDate);
						d2 = (Date)sdf.parse(today);
					} catch (ParseException e) {
						e.printStackTrace();
					}

					if(d1.getTime()<d2.getTime())
					{
						String userID = Integer.toString(rs.getInt("userID"));
						Statement stmtUpdt = sqlCon.conn.createStatement();
						int result = stmtUpdt.executeUpdate("UPDATE users SET cardStatus='frozen' WHERE userID='"+userID+"';");
						if(result==0)
						{
							res.add("Fail");
							break;
						}
					}
				}
				res.add("OK");
			}
			else
				res.add("Fail");
			rs.close();
			client.sendToClient(res);
		}
		/**
		 * Query controller for getting all the information about regular users 
		 */
		if(command.equals("UserReport"))
		{
			Statement stmt = null;
			try {
				stmt = sqlCon.conn.createStatement();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			ResultSet rs = stmt.executeQuery("SELECT * FROM users WHERE role='1';");
			if(rs.next())
			{
				res.add("OK");
				rs.previous();
				while(rs.next())
				{
					user= new User(rs.getInt("userID"),rs.getInt("id"), rs.getString("username"), rs.getString("password"), rs.getString("phone"), rs.getString("email"),
			                   rs.getString("role"), rs.getString("status"), rs.getString("cardStatus"));
			        res.add((Object)user);
				}
			}
			else res.add("Fail");
			rs.close();
			client.sendToClient(res);
		}
		/**
		 * Query controller for getting all the information about employees 
		 */
		if(command.equals("EmployeeReport"))
		{
			Statement stmt = null;
			try {
				stmt = sqlCon.conn.createStatement();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			ResultSet rs = stmt.executeQuery("SELECT * FROM users WHERE role='2' OR role='3';");
			if(rs.next())
			{
				res.add("OK");
				rs.previous();
				while(rs.next())
				{
					user= new User(rs.getInt("userID"),rs.getInt("id"), rs.getString("username"), rs.getString("password"), rs.getString("phone"), rs.getString("email"),
			                   rs.getString("role"), rs.getString("status"), rs.getString("cardStatus"));
			        res.add((Object)user);
				}
			}
			else res.add("Fail");
			rs.close();
			client.sendToClient(res);
		}
		
		if(command.equals("Activity"))
		{
			Statement stmt = null;
			try {
				stmt = sqlCon.conn.createStatement();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			String start = (String) messages.get(2);
			String end = (String) messages.get(3);
			
			ResultSet rs = stmt.executeQuery("SELECT COUNT(*) AS numOfOk FROM users WHERE role='1' AND cardStatus='ok' AND registrationDate>='"+start+"' AND registrationDate<='"+end+"';");
			if(rs.next())
			{
				String numOfOk=Integer.toString(rs.getInt("numOfOk"));
				res.add(numOfOk);
				
				rs = stmt.executeQuery("SELECT COUNT(*) AS numOfFrozen FROM users WHERE role='1' AND cardStatus='frozen';");
				if(rs.next())
				{
					String numOfFrozen=Integer.toString(rs.getInt("numOfFrozen"));
					res.add(numOfFrozen);
					
					rs = stmt.executeQuery("SELECT COUNT(*) AS numOfGraduate FROM users WHERE role='1' AND cardStatus='graduate';");
					if(rs.next())
					{
						String numOfGraduated=Integer.toString(rs.getInt("numOfGraduate"));
						res.add(numOfGraduated);
						
						rs = stmt.executeQuery("SELECT COUNT(*) AS activeBorrows FROM borrows WHERE borrowStatus='active' AND borrowDate>='"+start+"' AND borrowDate<='"+end+"';");
						if(rs.next())
						{
							String numOfActiveBorrows=Integer.toString(rs.getInt("activeBorrows"));
							res.add(numOfActiveBorrows);
							
							rs = stmt.executeQuery("SELECT DISTINCT borrows.userID, COUNT(*) AS numOfLates FROM borrows, errors WHERE errors.borrowErrorID=borrows.borrowID AND errors.errorDate>='"+start+"' AND errors.errorDate<='"+end+"';");
							if(rs.next())
							{
								String numOfLates=Integer.toString(rs.getInt("numOfLates"));
								res.add(numOfLates);
								res.add("OK");
							}
						}
					}
				}
			}
			rs.close();
			client.sendToClient(res);
		}
		/**
		 * Query controller for getting the borrow period for all non rated books
		 */
		if(command.equals("RegBooks"))
		{
            int value;
            Statement stmt;
            
            try{
                stmt = sqlCon.conn.createStatement();     
                ResultSet rs = stmt.executeQuery("SELECT borrows.borrowDate , borrows.actualReturnDate FROM borrows, books, bookcopies WHERE borrows.copyID=bookcopies.copyID AND books.bookCode=bookcopies.bookCode AND books.Rated='no';");
                if (rs.next())
                {
                	res.add("OK");
                        rs.previous();
                        while (rs.next())
                        {
                                Calendar d1 = Calendar.getInstance();
                                Calendar d2 = Calendar.getInstance();
                                d1.setTime(rs.getDate("borrowDate")) ;
                                d2.setTime(rs.getDate("actualReturnDate"));
                                long days=(d2.getTime().getTime() - d1.getTime().getTime()) / (24 * 3600 * 1000);
                                value=((int) Math.abs(days));
                                res.add(value);
                        }
                }
                else res.add("Fail");

                rs.close();
                    
            }
            catch (SQLException e) 
            {
                    e.printStackTrace();
                    res.add("Error");
            }
            
            client.sendToClient(res);

		}
		/**
		 * Query controller for getting the borrow period for all rated books
		 */
		if(command.equals("RatedBooks"))
		{
            int value;
            Statement stmt;
            
            try{
                stmt = sqlCon.conn.createStatement();     
                ResultSet rs = stmt.executeQuery("SELECT borrows.borrowDate , borrows.actualReturnDate FROM borrows, books, bookcopies WHERE borrows.copyID=bookcopies.copyID AND books.bookCode=bookcopies.bookCode AND books.Rated='yes';");
                if (rs.next())
                {
                	res.add("OK");
                        rs.previous();
                        while (rs.next())
                        {
                                Calendar d1 = Calendar.getInstance();
                                Calendar d2 = Calendar.getInstance();
                                d1.setTime(rs.getDate("borrowDate")) ;
                                d2.setTime(rs.getDate("actualReturnDate"));
                                long days=(d2.getTime().getTime() - d1.getTime().getTime()) / (24 * 3600 * 1000);
                                value=((int) Math.abs(days));
                                res.add(value);
                        }
                }
                else res.add("Fail");

                rs.close();
                    
            }
            catch (SQLException e) 
            {
                    e.printStackTrace();
                    res.add("Error");
            }
            
            client.sendToClient(res);

		}
	}

}
